﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace LaoLuo.Data.LayeredFramework
{
    public abstract partial class DataAccessLayerBase<T>
    {
        /// <summary>
        /// 获取IDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected IDataReader ExecuteReader(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            var tuple = GetDbCommand(sql, commandType, parameters);

            var ret = tuple.Item1.ExecuteReader(tuple.Item2);
            DisposeDbCommand(tuple.Item1, false);
            return ret;
        }

        /// <summary>
        /// 获取影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int ExecuteNonQuery(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            var tuple = GetDbCommand(sql, commandType, parameters);

            var ret = tuple.Item1.ExecuteNonQuery();
            DisposeDbCommand(tuple.Item1);
            return ret;
        }

        protected bool ExecuteNonQueryAffectSingleRow(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return ExecuteNonQuery(sql, commandType, parameters) == 1;
        }

        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected object ExecuteScalar(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            var tuple = GetDbCommand(sql, commandType, parameters);

            var ret = tuple.Item1.ExecuteScalar();
            DisposeDbCommand(tuple.Item1);
            return ret;
        }

        /// <summary>
        /// 返回存储过程return的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int GetReturnValue(string sql, params IDbDataParameter[] parameters)
        {
            var tuple = GetDbCommand(sql, CommandType.StoredProcedure, parameters);
            tuple.Item1.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int, 4) { Direction = ParameterDirection.ReturnValue });
            tuple.Item1.ExecuteNonQuery();
            var ret = (tuple.Item1.Parameters["@ReturnValue"] as IDbDataParameter).Value;
            DisposeDbCommand(tuple.Item1);
            return (int) ret;
        }
        
        /// <summary>
        /// 返回存储过程return的值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected T GetReturnValue<T>(string sql, params IDbDataParameter[] parameters)
        {
            var tuple = GetDbCommand(sql, CommandType.StoredProcedure, parameters);
            tuple.Item1.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int, 4) { Direction = ParameterDirection.ReturnValue });
            tuple.Item1.ExecuteNonQuery();
            var ret = (tuple.Item1.Parameters["@ReturnValue"] as IDbDataParameter).Value;
            DisposeDbCommand(tuple.Item1);
            return TypeConverter.ToType<T>(ret);
        }

        /// <summary>
        /// 返回第一行第一列的值的指定类型
        /// </summary>
        /// <typeparam name="TReturnType"></typeparam>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected TReturnType ExecuteScalar<TReturnType>(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return TypeConverter.ToType<TReturnType>(ExecuteScalar(sql, commandType, parameters));
        }

        /// <summary>
        /// 返回第一行第一列的值的long类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected long ExecuteInt64Scalar(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return TypeConverter.ToType<long>(ExecuteScalar(sql, commandType, parameters));
        }

        /// <summary>
        /// 返回第一行第一列的值的int类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int ExecuteInt32Scalar(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return TypeConverter.ToType<int>(ExecuteScalar(sql, commandType, parameters));
        }

        /// <summary>
        /// 返回第一行第一列的值的decimal类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected decimal ExecuteDecimalScalar(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return TypeConverter.ToType<decimal>(ExecuteScalar(sql, commandType, parameters));
        }

        /// <summary>
        /// 返回第一行第一列的值的string类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"> </param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected string ExecuteStringScalar(string sql, CommandType commandType, params IDbDataParameter[] parameters)
        {
            return TypeConverter.ToType<string>(ExecuteScalar(sql, commandType, parameters));
        }
    }

    /// <summary>
    /// base of data access layer
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public abstract partial class DataAccessLayerBase<T> : ITransactionControllerContainer<T>
        where T : TransactionController, new()
    {
        private bool _isNewConnection;
        private readonly Func<IDbConnection> _connectionFactory;
        private readonly Func<IDbCommand> _commandFactory;

        /// <summary>
        /// 当前的事务控制器
        /// </summary>
        public T TransactionController { get; private set; }

        protected DataAccessLayerBase(Func<IDbConnection> connectionFactory, Func<IDbCommand> commandFactory)
        {
            _connectionFactory = () =>
                                     {
                                         _isNewConnection = true;
                                         return connectionFactory();
                                     };

            _commandFactory = commandFactory;
        }

        /// <summary>
        /// 引用一个事务控制器,用此方法构建事务链
        /// </summary>
        /// <param name="transactionController"></param>
        public void ReferenceTransactionControllerFrom(T transactionController)
        {
            TransactionController = transactionController;
        }

        /// <summary>
        /// 引用一个事务控制器,用此方法构建事务链
        /// </summary>
        /// <param name="transactionControllerContainer"></param>
        public void ReferenceTransactionControllerFrom(ITransactionControllerContainer<T> transactionControllerContainer)
        {
            ReferenceTransactionControllerFrom(transactionControllerContainer.TransactionController);
        }

        //protected Tuple<int,IDataReader> GetPaginationResult(string sql, string orderBy, int pageSize, int pageIndex, int pageLimit, params IDbDataParameter[] parameters)

        #region protected

        /// <summary>
        /// 获取IDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected IDataReader ExecuteReader(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 获取影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int ExecuteNonQuery(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        protected bool ExecuteNonQueryAffectSingleRow(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteNonQueryAffectSingleRow(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected object ExecuteScalar(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值的指定类型
        /// </summary>
        /// <typeparam name="TReturnType"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected TReturnType ExecuteScalar<TReturnType>(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar<TReturnType>(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值的long类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected long ExecuteInt64Scalar(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar<long>(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值的int类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected int ExecuteInt32Scalar(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar<int>(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值的decimal类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected decimal ExecuteDecimalScalar(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar<decimal>(sql, CommandType.Text, parameters);
        }

        /// <summary>
        /// 返回第一行第一列的值的string类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected string ExecuteStringScalar(string sql, params IDbDataParameter[] parameters)
        {
            return ExecuteScalar<string>(sql, CommandType.Text, parameters);
        }

        #endregion

        private Tuple<IDbCommand, CommandBehavior> GetDbCommand(string sql, CommandType commandType = CommandType.Text, params IDbDataParameter[] parameters)
        {
            var dbCommand = _commandFactory();
            dbCommand.Parameters.Clear();
            var commandBehavior = CommandBehavior.Default;
            if (TransactionController != null && TransactionController.CurrentConnection != null && TransactionController.IsBegin) // transaction and connect exists
            {
                PrepareCommand(dbCommand, TransactionController.CurrentConnection, TransactionController.CurrentTransaction, sql, commandType, parameters);
            }
            else
            {
                PrepareCommand(dbCommand, _connectionFactory(), null, sql, commandType, parameters);
                commandBehavior = CommandBehavior.CloseConnection;
            }

            return new Tuple<IDbCommand, CommandBehavior>(dbCommand, commandBehavior);
        }

        private static void PrepareCommand(IDbCommand dbCommand, IDbConnection dbConnection, IDbTransaction dbTransaction, string commandText, CommandType commandType = CommandType.Text, params IDbDataParameter[] parameters)
        {
            if (dbConnection.State != ConnectionState.Open)
                dbConnection.Open();
            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = commandText;
            if (dbTransaction != null)
                dbCommand.Transaction = dbTransaction;
            dbCommand.CommandType = commandType;
            foreach (var parameter in parameters)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }

                dbCommand.Parameters.Add(parameter);
            }
        }

        private void DisposeDbCommand(IDbCommand dbCommand, bool closeNewConnection = true)
        {
            if (closeNewConnection && _isNewConnection && TransactionController == null)
            {
                dbCommand.Connection.Dispose();
            }
            dbCommand.Dispose();
            if (dbCommand.Parameters != null) dbCommand.Parameters.Clear();
        }
    }
}
